﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace iHub
{
    class AssignmentManager
    {
        private dbConnector db = new dbConnector();
        private SqlDataReader read;
        private SqlCommand command;

        public void InsertAssign(Assignment newAssignment)
        {
            db.connect();

            command = new SqlCommand("dbo.AddAssignment", db.getCon());
            command.CommandType = CommandType.StoredProcedure;

            //insert paramaters / values

            command.Parameters.AddWithValue("@e_id", newAssignment.Eid);
            command.Parameters.AddWithValue("@request", newAssignment.Request);
            command.Parameters.AddWithValue("@assign", newAssignment.Assign);
            command.Parameters.AddWithValue("@batch", newAssignment.Batch);
            command.Parameters.AddWithValue("@status", newAssignment.Status);
            command.Parameters.AddWithValue("@startdate", newAssignment.Startdate);
            command.Parameters.AddWithValue("@enddate", newAssignment.Enddate);
            command.Parameters.AddWithValue("@address", newAssignment.Address);
            command.Parameters.AddWithValue("@city", newAssignment.City);
            command.Parameters.AddWithValue("@ename", newAssignment.Ename);
            command.Parameters.AddWithValue("@type", newAssignment.Type);

            command.ExecuteNonQuery();

            db.disconnect();
        }

        public void UpdateAssign(Assignment newAssignment)
        {
            db.connect();

            command = new SqlCommand("dbo.EditAssignEmployee", db.getCon());
            command.CommandType = CommandType.StoredProcedure;

            //insert paramaters / values

            command.Parameters.AddWithValue("@e_id", newAssignment.Eid);
            command.Parameters.AddWithValue("@e_name", newAssignment.Ename);

            command.ExecuteNonQuery();
            db.disconnect();
        }

        public DataTable ViewAssign()
        {
            DataTable assignments = new DataTable();
            String dash = "-";

            assignments.Columns.Add(new DataColumn("Assignment No.", typeof(int)));
            assignments.Columns.Add(new DataColumn("Requesting Unit", typeof(string)));
            assignments.Columns.Add(new DataColumn("Batch No.", typeof(int)));
            assignments.Columns.Add(new DataColumn("Start Date", typeof(string)));
            assignments.Columns.Add(new DataColumn("End Date", typeof(string)));
            assignments.Columns.Add(new DataColumn("Address", typeof(string)));
            assignments.Columns.Add(new DataColumn("City/Municipality", typeof(string)));
            assignments.Columns.Add(new DataColumn("Employee", typeof(string)));
            assignments.Columns.Add(new DataColumn("Employee Id", typeof(int)));
            assignments.Columns.Add(new DataColumn("Description", typeof(string)));
            assignments.Columns.Add(new DataColumn("Status", typeof(string)));
            assignments.Columns.Add(new DataColumn("Type", typeof(int)));

            //   string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";
            db.connect();

            command = new SqlCommand("select * from Assignment", db.getCon());
            read = command.ExecuteReader();
            if (read.Read())
            {
                DataRow newRow = assignments.NewRow();
                int id;
                newRow["Assignment No."] = read.GetValue(0);
                newRow["Requesting Unit"] = read.GetValue(3);
                newRow["Batch No."] = read.GetValue(5);
                newRow["Start Date"] = read.GetValue(7);
                newRow["End Date"] = read.GetValue(8);
                newRow["Address"] = read.GetValue(9);
                newRow["City/Municipality"] = read.GetValue(10);
                newRow["Employee"] = read.GetValue(2);
                newRow["Employee Id"] = read.GetValue(1);
                newRow["Description"] = read.GetValue(4);
                newRow["Status"] = read.GetValue(6);
                newRow["Type"] = read.GetValue(11);


                assignments.Rows.Add(newRow);

                while (read.Read())
                {
                    DataRow newRow1 = assignments.NewRow();

                    newRow1["Assignment No."] = read.GetValue(0);
                    newRow1["Requesting Unit"] = read.GetValue(3);
                    newRow1["Batch No."] = read.GetValue(5);
                    newRow1["Start Date"] = read.GetValue(7);
                    newRow1["End Date"] = read.GetValue(8);
                    newRow1["Address"] = read.GetValue(9);
                    newRow1["City/Municipality"] = read.GetValue(10);
                    newRow1["Employee"] = read.GetValue(2);
                    newRow1["Employee Id"] = read.GetValue(1);
                    newRow1["Description"] = read.GetValue(4);
                    newRow1["Status"] = read.GetValue(6);
                    newRow1["Type"] = read.GetValue(11);
                    assignments.Rows.Add(newRow1);
                }

                read.Close();
                db.disconnect();
            }

            return assignments;
        }

        public DataTable FilterAssign(String a, String b)
        {
            String parameter = a;
            String parameter2 = b;
            DataTable filter = new DataTable();

            db.connect();

            filter = new DataTable();
            filter.Columns.Add(new DataColumn("Assignment No.", typeof(int)));
            filter.Columns.Add(new DataColumn("Requesting Unit", typeof(string)));
            filter.Columns.Add(new DataColumn("Batch No.", typeof(int)));
            filter.Columns.Add(new DataColumn("Start Date", typeof(string)));
            filter.Columns.Add(new DataColumn("End Date", typeof(string)));
            filter.Columns.Add(new DataColumn("Address", typeof(string)));
            filter.Columns.Add(new DataColumn("City/Municipality", typeof(string)));
            filter.Columns.Add(new DataColumn("Employee", typeof(string)));
            filter.Columns.Add(new DataColumn("Employee Id", typeof(int)));
            filter.Columns.Add(new DataColumn("Description", typeof(string)));
            filter.Columns.Add(new DataColumn("Status", typeof(string)));
            filter.Columns.Add(new DataColumn("Type", typeof(int)));

            command = new SqlCommand("select * from Assignment Where " + parameter2 + " LIKE '%" + parameter + "%'", db.getCon());
            read = command.ExecuteReader();
            // read.Read();

            if (read.Read())
            {
                filter.Rows.Clear();
                DataRow newRow = filter.NewRow();
                newRow["Assignment No."] = read.GetValue(0);
                newRow["Requesting Unit"] = read.GetValue(3);
                newRow["Batch No."] = read.GetValue(5);
                newRow["Start Date"] = read.GetValue(7);
                newRow["End Date"] = read.GetValue(8);
                newRow["Address"] = read.GetValue(9);
                newRow["City/Municipality"] = read.GetValue(10);
                newRow["Employee"] = read.GetValue(2);
                newRow["Employee Id"] = read.GetValue(1);
                newRow["Description"] = read.GetValue(4);
                newRow["Status"] = read.GetValue(6);
                newRow["Type"] = read.GetValue(11);
                filter.Rows.Add(newRow);
                while (read.Read())
                {
                    DataRow newRow1 = filter.NewRow();

                    newRow1["Assignment No."] = read.GetValue(0);
                    newRow1["Requesting Unit"] = read.GetValue(3);
                    newRow1["Batch No."] = read.GetValue(5);
                    newRow1["Start Date"] = read.GetValue(7);
                    newRow1["End Date"] = read.GetValue(8);
                    newRow1["Address"] = read.GetValue(9);
                    newRow1["City/Municipality"] = read.GetValue(10);
                    newRow1["Employee"] = read.GetValue(2);
                    newRow1["Employee Id"] = read.GetValue(1);
                    newRow1["Description"] = read.GetValue(4);
                    newRow1["Status"] = read.GetValue(6);
                    newRow1["Type"] = read.GetValue(11);
                    filter.Rows.Add(newRow1);
                }
                read.Close();
                db.disconnect();
            }
            return filter;
        }

        public void EditAssign(Assignment newAssignment)
        {
            db.connect();
            command = new SqlCommand("dbo.EditAssignment", db.getCon());
            command.CommandType = CommandType.StoredProcedure;

            //insert paramaters / values
            command.Parameters.AddWithValue("@assign_id", newAssignment.AssignId);
            command.Parameters.AddWithValue("@e_id", newAssignment.Eid);
            command.Parameters.AddWithValue("@e_name", newAssignment.Ename);
            command.Parameters.AddWithValue("@request", newAssignment.Request);
            command.Parameters.AddWithValue("@city", newAssignment.City);
            command.Parameters.AddWithValue("@address", newAssignment.Address);
            command.Parameters.AddWithValue("@description", newAssignment.Assign);
            command.Parameters.AddWithValue("@startdate", newAssignment.Startdate);
            command.Parameters.AddWithValue("@enddate", newAssignment.Enddate);
            command.Parameters.AddWithValue("@batchno", newAssignment.Batch);
            command.Parameters.AddWithValue("@status", newAssignment.Status);
            command.Parameters.AddWithValue("@type", newAssignment.Type);
            command.ExecuteNonQuery();
            db.disconnect();
        }

        public List<string> EmployeeAssign()
        {
            db.connect();
            //   DataSet list = new DataSet();
            command = new SqlCommand("select * from Employee Where e_status = 1 ORDER by e_name ASC", db.getCon());
            read = command.ExecuteReader();
            String name1, dash = "-";
            String item;

            //  list = new DataSet();
            int id1;

            //    DataRow list = new DataRow();
            List<string> _items = new List<string>();
            while (read.Read())
            {
                name1 = (string)read.GetValue(2);
                name1 = name1 += dash;
                id1 = (int)read.GetValue(0);
                item = name1 += id1;

                //Convert.ToString(id1);

                _items.Add(item);
            }

            read.Close();
            db.disconnect();

            return _items;
        }

        public DataTable ViewTable()
        {
            //  String parameter = a;
            // String parameter2 = b;
            DataTable filter = new DataTable();
            DataRow newRow1;

            db.connect();

            filter = new DataTable();
            filter.Columns.Add(new DataColumn("Assignment No.", typeof(int)));
            filter.Columns.Add(new DataColumn("Requesting Unit", typeof(string)));
            filter.Columns.Add(new DataColumn("Batch No.", typeof(int)));
            filter.Columns.Add(new DataColumn("Start Date", typeof(string)));
            filter.Columns.Add(new DataColumn("End Date", typeof(string)));
            filter.Columns.Add(new DataColumn("Address", typeof(string)));
            filter.Columns.Add(new DataColumn("City/Municipality", typeof(string)));
            filter.Columns.Add(new DataColumn("Employee", typeof(string)));
            filter.Columns.Add(new DataColumn("Employee Id", typeof(int)));
            filter.Columns.Add(new DataColumn("Description", typeof(string)));
            filter.Columns.Add(new DataColumn("Status", typeof(string)));
            filter.Columns.Add(new DataColumn("Type", typeof(int)));

            command = new SqlCommand("select * from Assignment", db.getCon());
            read = command.ExecuteReader();
            // read.Read();

            if (read.Read())
            {
                filter.Rows.Clear();
                DataRow newRow = filter.NewRow();
                newRow["Assignment No."] = read.GetValue(0);
                newRow["Requesting Unit"] = read.GetValue(3);
                newRow["Batch No."] = read.GetValue(5);
                newRow["Start Date"] = read.GetValue(7);
                newRow["End Date"] = read.GetValue(8);
                newRow["Address"] = read.GetValue(9);
                newRow["City/Municipality"] = read.GetValue(10);
                newRow["Employee"] = read.GetValue(2);
                newRow["Employee Id"] = read.GetValue(1);
                newRow["Description"] = read.GetValue(4);
                newRow["Status"] = read.GetValue(6);
                newRow["Type"] = read.GetValue(11);
                filter.Rows.Add(newRow);
                while (read.Read())
                {
                    newRow1 = filter.NewRow();

                    newRow1["Assignment No."] = read.GetValue(0);
                    newRow1["Requesting Unit"] = read.GetValue(3);
                    newRow1["Batch No."] = read.GetValue(5);
                    newRow1["Start Date"] = read.GetValue(7);
                    newRow1["End Date"] = read.GetValue(8);
                    newRow1["Address"] = read.GetValue(9);
                    newRow1["City/Municipality"] = read.GetValue(10);
                    newRow1["Employee"] = read.GetValue(2);
                    newRow1["Employee Id"] = read.GetValue(1);
                    newRow1["Description"] = read.GetValue(4);
                    newRow1["Status"] = read.GetValue(6);
                    newRow1["Type"] = read.GetValue(11);
                    filter.Rows.Add(newRow1);
                }
                read.Close();
                db.disconnect();
            }
            return filter;
        }

        public DataTable ViewTable1(DataTable s)
        {
            //  String parameter = a;
            // String parameter2 = b;
            DataTable filter = s;

            db.connect();

            filter = new DataTable();
            filter.Columns.Add(new DataColumn("Assignment No.", typeof(int)));
            filter.Columns.Add(new DataColumn("Requesting Unit", typeof(string)));
            filter.Columns.Add(new DataColumn("Batch No.", typeof(int)));
            filter.Columns.Add(new DataColumn("Start Date", typeof(string)));
            filter.Columns.Add(new DataColumn("End Date", typeof(string)));
            filter.Columns.Add(new DataColumn("Address", typeof(string)));
            filter.Columns.Add(new DataColumn("City/Municipality", typeof(string)));
            filter.Columns.Add(new DataColumn("Employee", typeof(string)));
            filter.Columns.Add(new DataColumn("Employee Id", typeof(int)));
            filter.Columns.Add(new DataColumn("Description", typeof(string)));
            filter.Columns.Add(new DataColumn("Status", typeof(string)));
            filter.Columns.Add(new DataColumn("Type", typeof(int)));

            command = new SqlCommand("select * from Assignment", db.getCon());
            read = command.ExecuteReader();
            // read.Read();

            if (read.Read())
            {
                filter.Rows.Clear();
                DataRow newRow = filter.NewRow();
                newRow["Assignment No."] = read.GetValue(0);
                newRow["Requesting Unit"] = read.GetValue(3);
                newRow["Batch No."] = read.GetValue(5);
                newRow["Start Date"] = read.GetValue(7);
                newRow["End Date"] = read.GetValue(8);
                newRow["Address"] = read.GetValue(9);
                newRow["City/Municipality"] = read.GetValue(10);
                newRow["Employee"] = read.GetValue(2);
                newRow["Employee Id"] = read.GetValue(1);
                newRow["Description"] = read.GetValue(4);
                newRow["Status"] = read.GetValue(6);
                newRow["Type"] = read.GetValue(11);

                filter.Rows.Add(newRow);
                while (read.Read())
                {
                    DataRow newRow1 = filter.NewRow();

                    newRow1["Assignment No."] = read.GetValue(0);
                    newRow1["Requesting Unit"] = read.GetValue(3);
                    newRow1["Batch No."] = read.GetValue(5);
                    newRow1["Start Date"] = read.GetValue(7);
                    newRow1["End Date"] = read.GetValue(8);
                    newRow1["Address"] = read.GetValue(9);
                    newRow1["City/Municipality"] = read.GetValue(10);
                    newRow1["Employee"] = read.GetValue(2);
                    newRow1["Employee Id"] = read.GetValue(1);
                    newRow1["Description"] = read.GetValue(4);
                    newRow1["Status"] = read.GetValue(6);
                    newRow1["Type"] = read.GetValue(11);
                    filter.Rows.Add(newRow1);
                }
                read.Close();
                db.disconnect();
            }
            return filter;
        }

        public bool typeCheck(int rate)
        {
            bool check = true;
            int payment = rate;

            db.connect();

            command = new SqlCommand("select payment from Assignment_Type where payment = "+rate, db.getCon());
            read = command.ExecuteReader();

            if (read.Read())
            {
                check = false;
            }

            db.disconnect();

            return check;
        }

        public void addRate(int rate)
        {
            int payment = rate;

            db.connect();

            command = new SqlCommand("INSERT INTO Assignment_Type (payment) VALUES (@pay)", db.getCon());
            command.Parameters.Add(new SqlParameter("pay", payment));

            command.ExecuteNonQuery();

            db.disconnect();
        }

        public List<string> getRates()
        {
            List<string> rates = new List<string>();
            string i = "";
            string i2 = "";
            string rate = "";
            string dash = "-";

            db.connect();

            command = new SqlCommand("select * from Assignment_Type", db.getCon());
            read = command.ExecuteReader();

            try
            {
                while (read.Read())
                {
                    i = Convert.ToString(read["type"]);
                    i2 = Convert.ToString(read["payment"]);
                    rate = i + dash + i2;
                    rates.Add(rate);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("No payment rates available","Error",MessageBoxButtons.OK,MessageBoxIcon.Stop);
            }

            db.disconnect();

            return rates;
        }

        public void modRate(int rate, int type)
        {
            int payment = rate;
            int t = type;

            db.connect();

            command = new SqlCommand("update Assignment_Type set payment = @pay where type = @type", db.getCon());
            command.Parameters.Add(new SqlParameter("pay", payment));
            command.Parameters.Add(new SqlParameter("type", t));

            command.ExecuteNonQuery();

            db.disconnect();
        }
    }
}